import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.Iterator;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
class XlsToCmdReadVertically {
public static void main(String[] args) throws NullPointerException, java.io.FileNotFoundException, java.io.IOException {
new insertIntoDB();
if (args.length < 4) {
System.out.println("Give command line arguments: xlsFileName Degree Year RowContaingSectionsNames");
System.exit(0);
}
String degree = args[1];
short year = (short) Integer.parseInt(args[2]);
short RowContaingSectionsNames = (short) Integer.parseInt(args[3]);
InputStream inp = new FileInputStream(args[0]);
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
HSSFSheet sheet = wb.getSheetAt(0);
noOfMR = sheet.getNumMergedRegions();
//System.out.println("no of MR is "+ noOfMR);
//initializes mergeRanges[] and split all the merge areas which dont have any content:
for (int i = 0; i < noOfMR; ++i) {
CellRangeAddress MR = null;
MR = sheet.getMergedRegion(i);
if (MR == null) {
continue;//System.out.println("MR is null");//
}
String str = null;
int rowMR = MR.getFirstRow();
int colMR = MR.getFirstColumn();
for (Row r_ : sheet) {//read MR.getFirstColumn(), MR.getFirstRow()
if (r_.getRowNum() != rowMR) {
continue;
}
for (Cell c_ : r_) {
if (c_.getColumnIndex() != colMR) {
continue;
}
switch (c_.getCellType()) {
case Cell.CELL_TYPE_STRING:
if (!((c_.getRichStringCellValue().getString()).equals(""))) {
str = c_.getRichStringCellValue().getString();//System.out.println(cellRef.formatAsString()+" "+cell.getRichStringCellValue().getString());//"Row: "+row.getRowNum()+" Cell: "+cell.getColumnIndex()+" "+cellRef.formatAsString()+" - <In String> "+cell.getRichStringCellValue().getString());
}
break;
case Cell.CELL_TYPE_NUMERIC:
if (!((((Double) (c_.getNumericCellValue())).toString()).equals(""))) {
str = Double.toString(c_.getNumericCellValue());
}
break;
default:
str = null;//System.out.println(" ");//("<In Default>");
}//switch
}//cell
}//row
if (str == null)//read MR.getFirstColumn(), MR.getFirstRow(), if contents == null, then remove merged region.
{
sheet.removeMergedRegion(i);
}
}
noOfMR = sheet.getNumMergedRegions();
mergeRange = new CellRangeAddress[noOfMR];
//initializes mergeRanges[]:
for (int i = 0; i < noOfMR; ++i) {
mergeRange[i] = sheet.getMergedRegion(i);
}
//count and initialize noOfSections
breakHere:
for (Row rr : sheet) {
if (rr.getRowNum() < 6) {
continue;
}
int maxCellInd = -1;
for (Cell cc : rr) {
maxCellInd = /*index*/ cc.getColumnIndex();
}
noOfSections = (1 + maxCellInd) - 2/*day period*/;
noOfSections /= 2; //each section consumes 2 columns
break breakHere;
}
//Read section names
sectionsNames = new String[noOfSections];
breakOut:
for (Row rr : sheet) {
if (rr.getRowNum() < (6 - 1)) {
continue;
}
for (Cell cc : rr) {
if (cc.getColumnIndex() < 2 || cc.getColumnIndex() % 2 == 1) {
continue;
}
sectionsNames[(cc.getColumnIndex() - 2) / 2] = cc.getRichStringCellValue().getString();
}
break breakOut;
}
/*output section names
for(int countt=0; countt<noOfSections; countt++)
System.out.println(sectionsNames[countt]);*/
//choose section:
for (int sec = 0; sec < noOfSections; sec++) {
//Cells Traversal for the choosen section:
for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
//for each session (means class/tut/lab)
int lastColIndexForRow1 = -1;
int startingColIndexForRow1 = -1;
CellRangeAddress currCellRange = null;
boolean classForSingleSection = false;//initialized by row1
boolean cellsFromRow2AreInGroupOf2WithEachOther = false;//initialized by row2//true means its a lab
//Row 1
if (!(rit.hasNext())) {
continue;
}
Row row = rit.next();
System.out.println(1 + row.getRowNum() + "------------------------------");
if (row.getRowNum() < RowContaingSectionsNames) {
continue;//rows before that one containing list of section names need not to be traversed.
} //System.out.println("myRorNo : "+row);
for (int coun = 0; coun < 3; coun++) {
session[coun] = null;
}
session[SECTION] = sectionsNames[sec];
switch (((row.getRowNum() - 6) / 2) / 10) {
case 0:
session[DAY] = "Mon";
break;
case 1:
session[DAY] = "Tue";
break;
case 2:
session[DAY] = "Wed";
break;
case 3:
session[DAY] = "Thu";
break;
case 4:
session[DAY] = "Fri";
break;
//default: session[DAY] = "Sun";
}
session[PERIOD] = Integer.toString(((row.getRowNum() - 6) / 2) % 10 + 1);
for (Cell cell : row) {
//Only allow if colNo satisfies that of the section chosen above:
if (cell.getColumnIndex() < sec * 2 + 2)//4)//sec*2+2
{
continue;
}
//else if(cell.getColumnIndex() > sec*2+2)//5))
//break;
//If it is 1st column, then do...
//if(cell.getColumnIndex() % 2 ==0)// == 4)
{
lastColIndexForRow1 = -1;
startingColIndexForRow1 = -1;
currCellRange = null;
currCellRange = findMergedRange(row.getRowNum(), cell.getColumnIndex());//check the mergedCellArea to which this cell belongs.
if (currCellRange != null) {
if (currCellRange.getFirstColumn() == cell.getColumnIndex() &&
currCellRange.getLastColumn() == 1 + cell.getColumnIndex())//check here if this class is for single section. If yes, set the variable to true
{
classForSingleSection = true;
}
startingColIndexForRow1 = currCellRange.getFirstColumn();
lastColIndexForRow1 = currCellRange.getLastColumn();
//System.out.println("classForSingleSection: "+classForSingleSection+"\nstartingColIndexForRow1: "+startingColIndexForRow1);
}
}
//CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING://if this cell is first in merged area, then do this:
if (!((cell.getRichStringCellValue().getString()).equals(""))) {
session[COURSE] = cell.getRichStringCellValue().getString();//System.out.println(cellRef.formatAsString()+" "+cell.getRichStringCellValue().getString());//"Row: "+row.getRowNum()+" Cell: "+cell.getColumnIndex()+" "+cellRef.formatAsString()+" - <In String> "+cell.getRichStringCellValue().getString());
}
break;
default://check if this cell is merged? if yes, get course name
if (currCellRange != null//makes sure that the current cell is in merged region and not single
//&& currCellRange.getNumberOfCells()>2//not just for a single section
&& currCellRange.getFirstColumn() != cell.getColumnIndex())//it is not the starting cell of the merged area
{
for (Row r_ : sheet) {//read contents of cell(row.getRowNum(), currCellRange.getFirstColumn())
if (r_.getRowNum() != row.getRowNum()) {
continue;
}
for (Cell c_ : r_) {
if (c_.getColumnIndex() != currCellRange.getFirstColumn()) {
continue;
}
if (!((c_.getRichStringCellValue().getString()).equals(""))) {
session[COURSE] = c_.getRichStringCellValue().getString();//System.out.println(cellRef.formatAsString()+" "+c_.getRichStringCellValue().getString());
} else {
session[COURSE] = null;//System.out.println("<Free Period>");//("<In Default>");
}
}
}
} else {
session[COURSE] = null;//System.out.println("<Free Period>");//("<In Default>");
}
}//switch
break;
}//for cell
//Row 2:
currCellRange = null;
if (!(rit.hasNext())) {
continue;
}
row = rit.next();
for (Cell cell : row) {
//Only allow traversal of cells for 2 specific cols which corresponds to the chosen section.
if (cell.getColumnIndex() < sec * 2 + 2)//4)//sec*2+2
{
continue;
} else if (cell.getColumnIndex() > sec * 2 + 3)//5))
{
break;
}
//set variable cellsFromRow2AreInGroupOf2WithEachOther here.//=true means it's a lab or a two hour tut.
if (cell.getColumnIndex() % 2 == 0 /*== 4*/ && classForSingleSection == true)//only set if classForSingleSection is true
{
currCellRange = findMergedRange(row.getRowNum(), cell.getColumnIndex());//check the mergedCellArea to which this cell belongs.
if (currCellRange != null && currCellRange.getFirstColumn() == cell.getColumnIndex() && currCellRange.getLastColumn() == 1 + cell.getColumnIndex()) {
cellsFromRow2AreInGroupOf2WithEachOther = true;//check and initialize cellsFromRow2AreInGroupOf2WithEachOther
}
}
{
if (cellsFromRow2AreInGroupOf2WithEachOther == true)//lab or two hr tut. 2 row merged of 2 cells
{
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
session[ROOM] = cell.getRichStringCellValue().getString();
break;
default:
System.out.println("in default asdfy");
session[ROOM] = "";
}
break;
} else if (classForSingleSection == true)//tut for 1 section
{
if (cell.getColumnIndex() % 2 == 0)//left
{
session[ROOM] = cell.getRichStringCellValue().getString();
} else//right
{
session[TEACHER] = cell.getRichStringCellValue().getString();
}
} else//lec for multiple sections
{
if (cell.getColumnIndex() % 2 == 0)//left
{
for (Row r_ : sheet) {
if (r_.getRowNum() != row.getRowNum()) {
continue;
}
for (Cell c_ : r_) {
if (c_.getColumnIndex() != startingColIndexForRow1) {
continue;
}
//if(!((c_.getRichStringCellValue().getString()).equals("")))
session[ROOM] = c_.getRichStringCellValue().getString();
}
}
} else//right
{
for (Row r_ : sheet) {
if (r_.getRowNum() != row.getRowNum()) {
continue;
}
for (Cell c_ : r_) {
if (c_.getColumnIndex() != lastColIndexForRow1) {
continue;
}
//System.out.println("I am here 277");
//if(!((c_.getRichStringCellValue().getString()).equals("")))
session[TEACHER] = c_.getRichStringCellValue().getString();
}
}
}
}
}
}//for cell
if (!classForSingleSection) {
try {
if (session[COURSE] != null)//if(!((session[COURSE]+session[ROOM]+session[TEACHER]).equals("")))
{
insertIntoDB.insert(session, degree, year);
//System.out.println("Cour."+session[COURSE]+"\nRoom."+session[ROOM]+"\nTeac."+session[TEACHER]+
//"\nSect."+session[SECTION]+"\nDay ."+session[DAY]+"\nPeri."+session[PERIOD]);
}
} catch (SQLException e) {
System.out.println(e.toString());
}
continue;
}
if (!cellsFromRow2AreInGroupOf2WithEachOther) {
try {
if (session[COURSE] != null)//if(!((session[COURSE]+session[ROOM]+session[TEACHER]).equals("")))
{
insertIntoDB.insert(session, degree, year);
//System.out.println("Cour."+session[COURSE]+"\nRoom."+session[ROOM]+"\nTeac."+session[TEACHER]+
//"\nSect."+session[SECTION]+"\nDay ."+session[DAY]+"\nPeri."+session[PERIOD]);
}
} catch (Exception e) {
System.out.println(e.toString());
}
continue;//if cells from row 2 are not in group of 2 with each other then continue
//if the Row3 and Row4 code is run, it means there is a lab.
}
//Row3
if (!(rit.hasNext())) {
continue;
}
row = rit.next();
for (Cell cell : row) {
if (cell.getColumnIndex() < sec * 2 + 2) {
continue;
} else if ((cell.getColumnIndex() > sec * 2 + 3)) {
break;
}
CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
if (!((cell.getRichStringCellValue().getString()).equals(""))) {
session[ROOM] = session[ROOM] + " " + cell.getRichStringCellValue().getString();//System.out.println(cellRef.formatAsString()+" "+cell.getRichStringCellValue().getString());//"Row: "+row.getRowNum()+" Cell: "+cell.getColumnIndex()+" "+cellRef.formatAsString()+" - <In String> "+cell.getRichStringCellValue().getString());
}
break;
//default:
//System.out.println(" ");//("<In Default>");
}//switch
break;
}//for cell
//Row4
if (!(rit.hasNext())) {
continue;
}
row = rit.next();
boolean flag3hrLab = false;
for (Cell cell : row) {
if (cell.getColumnIndex() < sec * 2 + 2) {
continue;
} else if ((cell.getColumnIndex() > sec * 2 + 3)) {
break;
}
//check if this(4th row is merged for two cells? if yes, then lab else 2 hr tut)
CellRangeAddress abc = null;
abc = findMergedRange(row.getRowNum(), cell.getColumnIndex());
if (cell.getColumnIndex() % 2 == 0) {//left cell
isItTut = false;
if (abc != null && abc.getFirstColumn() == cell.getColumnIndex() && abc.getLastColumn() == 1 + cell.getColumnIndex()) {
isItTut = false;
} else {
isItTut = true;
}
}
//System.out.println("tut?"+isItTut);
if (isItTut == false)//if lab
{
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
if (!((cell.getRichStringCellValue().getString()).equals(""))) {
session[TEACHER] = cell.getRichStringCellValue().getString();//System.out.println(cellRef.formatAsString()+" "+cell.getRichStringCellValue().getString());//"Row: "+row.getRowNum()+" Cell: "+cell.getColumnIndex()+" "+cellRef.formatAsString()+" - <In String> "+cell.getRichStringCellValue().getString());
}
break;
}//switch
break;
} else//if tut
{
if (cell.getColumnIndex() % 2 == 0) {//left
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
if (!((cell.getRichStringCellValue().getString()).equals(""))) {
session[TEACHER] = cell.getRichStringCellValue().getString();//System.out.println(cellRef.formatAsString()+" "+cell.getRichStringCellValue().getString());//"Row: "+row.getRowNum()+" Cell: "+cell.getColumnIndex()+" "+cellRef.formatAsString()+" - <In String> "+cell.getRichStringCellValue().getString());
}
break;
}//switch
} else//right
{
session[COURSE] = session[COURSE] + session[ROOM];
session[ROOM] = session[TEACHER];
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
if (!((cell.getRichStringCellValue().getString()).equals(""))) {
session[TEACHER] = cell.getRichStringCellValue().getString();//System.out.println(cellRef.formatAsString()+" "+cell.getRichStringCellValue().getString());//"Row: "+row.getRowNum()+" Cell: "+cell.getColumnIndex()+" "+cellRef.formatAsString()+" - <In String> "+cell.getRichStringCellValue().getString());
}
break;
}//switch
}
}
if (session[TEACHER] != null && session[TEACHER].equals("LAB"))//3 hr lab
{
flag3hrLab = true;
session[ROOM] = session[ROOM] + session[TEACHER];
if (!(rit.hasNext())) {
continue;
}
row = rit.next();
if (!(rit.hasNext())) {
continue;
}
row = rit.next();
//read r,c for seesion[TEACHER];
breakheres:
for (Row r_ : sheet) {
if (r_.getRowNum() != row.getRowNum()) {
continue;
}
for (Cell c_ : r_) {
if (c_.getColumnIndex() != startingColIndexForRow1) {
continue;
}
//System.out.println("I am here 277");
//if(!((c_.getRichStringCellValue().getString()).equals("")))
session[TEACHER] = c_.getRichStringCellValue().getString();
break breakheres;
}
}
}
}//for cell
if (session[COURSE] != null && !(session[COURSE].equals("null")) && !(session[COURSE].equals(null)) && !(session[COURSE].equals("")))//if(!((session[COURSE]+session[ROOM]+session[TEACHER]).equals("")))
{
System.out.println("Cour." + session[COURSE] + "\nRoom." + session[ROOM] + "\nTeac." + session[TEACHER] + "\nSect." + session[SECTION] + "\nDay ." + session[DAY] + "\nPeri." + session[PERIOD]);
System.out.println((row.getRowNum() - ((flag3hrLab) ? (2) : (0))) + "------------------------------");
System.out.println("Cour." + session[COURSE] + "\nRoom." + session[ROOM] + "\nTeac." + session[TEACHER] + "\nSect." + session[SECTION] + "\nDay ." + session[DAY] + "\nPeri." + (1 + Integer.parseInt(session[PERIOD])));
if (flag3hrLab) {
System.out.println(row.getRowNum() + "------------------------------");
System.out.println("Cour." + session[COURSE] + "\nRoom." + session[ROOM] + "\nTeac." + session[TEACHER] + "\nSect." + session[SECTION] + "\nDay ." + session[DAY] + "\nPeri." + (1 + Integer.parseInt(session[PERIOD])));
}
}
}//for rows of chosen section
}//for choosing 1 section
}//main
static CellRangeAddress findMergedRange(int rowInd, int colInd)//check if rowInd,colInd isinany mergeRange
{
CellRangeAddress cellRange = null;
for (int i = 0; i < noOfMR; ++i) {
if (mergeRange[i].isInRange(rowInd, colInd)) {
return mergeRange[i];
}
}
return null;
}
static boolean isItTut;
static String[] sectionsNames;
static int noOfSections = 0;
static int COURSE = 0, ROOM = 1, TEACHER = 2, SECTION = 3, DAY = 4, PERIOD = 5;
static String[] session = new String[6];
static int noOfMR;
static CellRangeAddress[] mergeRange;
}//class